package es.ats.codington.festival.daos;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.exceptions.DAOSExceptions;
import es.ats.codington.festival.interfaces.daos.iVisitorDAO;
import es.ats.codington.festival.utils.FERSDataConnection.FersDC;
import es.ats.codington.festival.utils.Query.JSonQuery;

public class VisitorDAOImpl implements iVisitorDAO {
	
	private Connection connection;
	private PreparedStatement statement;
	private ResultSet resultSet;
	/**JSonQuery json=new JSonQuery();**/
	
	/**
	 * Constructor 
	 * @param connection
	 */
	public VisitorDAOImpl(Connection connection) {
		super();
		this.connection = connection;
	}
	
	public VisitorDAOImpl() {
	}

	
	/**
	 * @param visitante1
	 * @return
	 * @throws SQLException 
	 */
	@Override
	public boolean insertVisitor(Visitor visitante1) throws  Exception {
		//JSonQuery json=new JSonQuery();
		int affectedRows=0;
		
		try{
			
			//Setting commit is mandatory
			connection.setAutoCommit(false);

			
			//Inserting new People in the database
			statement = connection.prepareStatement("INSERT INTO  `codingtonpojodb`.`people` (`name`, `lastname`, `email`, `address`, `phone`,`dni`)VALUES(?,?,?,?,?,?) ",statement.RETURN_GENERATED_KEYS);
			statement.setString(1, visitante1.getName());
			statement.setString(2, visitante1.getLastname());
			statement.setString(3, visitante1.getMail());
			statement.setString(4, visitante1.getAddress());
			statement.setLong(5, visitante1.getPhone());
			statement.setString(6, visitante1.getDni());
						
			affectedRows=statement.executeUpdate();
						
			//Getting the idpeople
			ResultSet lis = statement.getGeneratedKeys();
			lis.next();  
			int primarykey = lis.getInt(1);
		    
			//Inserting new Visitor in the database
			PreparedStatement myVisitor = connection.prepareStatement("INSERT INTO  `codingtonpojodb`.`visitor` (`usuname`, `password`, `idvisitor` )VALUES(?,?,?)");
			myVisitor.setString(1, visitante1.getUsername());
			myVisitor.setString(2, visitante1.getPassword());
			myVisitor.setInt(3, primarykey);
			int rs= 0;
			

			//Getting the list of visitors
			PreparedStatement otherVisitor = connection.prepareStatement("SELECT usuname FROM visitor");
			ResultSet otherVisitorName = null;
			otherVisitorName = otherVisitor.executeQuery();
			
			//Checking for repetitions
			while(otherVisitorName.next()){
				if(visitante1.getUsername().equals(otherVisitorName.getString(1))){
					throw new Exception("This Username is not available");
				}
			}	
			rs=myVisitor.executeUpdate();

			connection.commit();
			affectedRows= statement.executeUpdate();
			
			if(affectedRows!=0){
				return true;
			}
			else{
				return false;
			}
		
		}catch (Exception ex){
			throw ex;
			
		}
		
	
	}

	
	
	/**
	 * @param visitante2
	 * @return
	 */
	@Override 
	public int loginVisitor(Visitor visitante2) throws Exception{
		int idvisitor=0;	

		try{	
	
			//Setting commit is mandatory
			connection.setAutoCommit(false);
			
			//Getting the list of Visitor
			statement = connection.prepareStatement("SELECT idvisitor FROM visitor WHERE usuname=? AND password=?");
			statement.setString(1,visitante2.getUsername());
			statement.setString(2,visitante2.getPassword());
			
			resultSet=statement.executeQuery();
			while(resultSet.next()){
				idvisitor=resultSet.getInt(1);
			}
			if(idvisitor==0){
				throw new Exception("Username or password incorrect");
				
			}
	
		  	return idvisitor;
			
		}catch (Exception ex) {
			throw ex;
			
		}
		
			
		
	}
	
	/**
	 * @param visitante3
	 * @return
	 */
	@Override
	public boolean updateVisitor(Visitor visitante3, int idvisitor) throws Exception{
		/**JSonQuery json=new JSonQuery();**/
		int affectedRows=0;
				
		try{
			connection = FersDC.getConnection();
			if (connection == null) throw new IOException("It does not connect to DataBase");
			
			//Setting commit is mandatory
			connection.setAutoCommit(false);
			
			//Getting data of the Visitor online
			PreparedStatement myVisitor = connection.prepareStatement("SELECT * FROM visitor WHERE idvisitor = ? ");
			myVisitor.setInt(1, idvisitor);
			
			//First checking the password and username
			ResultSet myVisitorSet = null;
			myVisitorSet = myVisitor.executeQuery();
			myVisitorSet.next();
			String[] data = {myVisitorSet.getString(1), myVisitorSet.getString(2)};
			
			if(visitante3.getUsername().equals(data[0]) && visitante3.getPassword().equals(data[1]) ){
			}else{
				throw new Exception("Username or Password incorrect");
			}
			
			// Now making changes			
			PreparedStatement myPeople = connection.prepareStatement("UPDATE people SET name= ?, lastname= ? , email = ? , address = ? , phone = ? , dni = ?  WHERE idpeople= ? ");
			myPeople.setString(1, visitante3.getName());
			myPeople.setString(2, visitante3.getLastname());
			myPeople.setString(3, visitante3.getMail());
			myPeople.setString(4, visitante3.getAddress());
			myPeople.setLong(5, visitante3.getPhone());
			myPeople.setString(6, visitante3.getDni());
			myPeople.setInt(7, idvisitor);

			myPeople.executeUpdate();
			connection.commit();

			affectedRows= myPeople.executeUpdate();
			
		
		}catch (Exception ex){
			throw ex;
			
		}
		if(affectedRows!=0){
			return true;
		}
		else{
			return false;
		}
	}



	@Override
	public boolean updatePass(Visitor visitante4, String newpassword, int idvisitor) throws Exception {
		//JSonQuery json=new JSonQuery();
		int affectedRows = 0;		

		try{
			connection = FersDC.getConnection();
			if (connection == null) throw new IOException("It does not connect to DataBase");
		
			//Setting commit is mandatory
			connection.setAutoCommit(false);
			
			//Getting data of the Visitor online
			PreparedStatement myVisitor = connection.prepareStatement("SELECT * FROM visitor WHERE idvisitor = ? ");
			myVisitor.setInt(1, idvisitor);

			//First checking the password and username
			ResultSet myVisitorSet = null;
			myVisitorSet = myVisitor.executeQuery();
			myVisitorSet.next();
			String[] data = {myVisitorSet.getString(1), myVisitorSet.getString(2)};

			
			if(visitante4.getUsername().equals(data[0]) && visitante4.getPassword().equals(data[1]) ){
			}else{
				throw new Exception("Username or Password incorrect");
			}

			// Now making changes			
			PreparedStatement myNewVisitor = connection.prepareStatement(" UPDATE visitor SET password = ?  WHERE idvisitor = ?  ");
			myNewVisitor.setString(1, newpassword);
			myNewVisitor.setInt(2, idvisitor);

			myNewVisitor.executeUpdate();
			connection.commit();
	
			affectedRows= myNewVisitor.executeUpdate();
			
		
		}catch (Exception ex){
			connection.rollback();
			throw ex;
			
		}
		if(affectedRows!=0){
			return true;
		}
		else{
			return false;
		}
	}
	
	 @Override
	public boolean isAdmin(Visitor visitante5) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}
}